package dao;

import bean.CAF;
import bean.Page;
import util.JdbcUtils;
import util.StringUtil;
import util.Connect;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * 咨询反馈的DAO【增删改查】
 *
 * @author dsf
 * @date|time 2017/9/8|23:12
 * @last update:
 */
public class CAFDao {

    /**
     * 咨询反馈增加
     *
     * @param caf
     * @return
     * @throws Exception
     */
    public static int cafAdd(CAF caf, Connect conn) throws Exception {
        StringBuffer sb = new StringBuffer("insert into egov_consulting_and_feedback values( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
        List<Object> para = new ArrayList<Object>();

        para.add(caf.getCafId());
        para.add(caf.getCafTitle());
        para.add(caf.getDepId());
        para.add(caf.getCafType());
        para.add(caf.getCafPublic());
        para.add(caf.getName());
        para.add(caf.getPhone());
        para.add(caf.getAddress());
        para.add(caf.getEmail());
        para.add(caf.getCafContent());
        para.add(caf.getCafFile());
        para.add(caf.getCafPostDate());
        para.add(caf.getCafReplyDate());
        para.add(caf.getCafSolvedStatus());
        para.add(caf.getCafReply());
        para.add(caf.getCafSearchKey());

        String sql = sb.toString();
        System.out.println(sql);
        return conn.update(sql, para);
    }


    /**
     * 查询操作
     *
     * @param caf
     * @param conn
     * @return
     * @throws Exception
     */
    public static List<CAF> search(CAF caf, Page page, Connect conn) throws Exception {
        StringBuffer sb = new StringBuffer("select * from ");
        //限定对公共留言的查看数目
        if (StringUtil.isNotEmpty(caf.getCafPublic())) {
            sb.append("(select * from egov_consulting_and_feedback caf0 limit 100) caf1 where ");
        } else {
            sb.append("egov_consulting_and_feedback caf1 where ");
        }
        List<Object> para = new ArrayList<Object>();
        if (StringUtil.isNotEmpty(caf.getCafId())) {
            sb.append("caf1.cafId=?");
            para.add(caf.getCafId());
        }
        if (StringUtil.isNotEmpty(caf.getCafTitle()) && StringUtil.isEmpty(caf.getCafType())) {
            sb.append("caf1.cafTitle like ? and ");
            para.add("%" + caf.getCafTitle() + "%");
        }
        if (StringUtil.isNotEmpty(caf.getCafType())) {
            sb.append("caf1.cafType=? and ");
            para.add(caf.getCafType());
        }
        if (StringUtil.isNotEmpty(caf.getCafTitle()) && StringUtil.isNotEmpty(caf.getCafType())) {
            sb.append("caf1.cafTitle like ? and caf1.cafType=? and ");
            para.add("%" + caf.getCafTitle() + "%");
            para.add(caf.getCafType());
        }
        if (StringUtil.isNotEmpty(caf.getCafSearchKey())) {
            sb.append("caf1.cafSearchKey=?");
            para.add(caf.getCafSearchKey());
        }
        //限定对公共留言的查看时间（1年内）
        if (StringUtil.isNotEmpty(caf.getCafPublic())) {
            sb.append("caf1.cafPublic=? and date_sub(curdate(), INTERVAL 1 YEAR) <= date(`cafPostDate`) order by caf1.cafPostDate desc");
            para.add(caf.getCafPublic());
        }
        if (StringUtil.isNotEmpty(caf.getCafSolvedStatus())) {
            sb.append("caf1.cafSolvedStatus=? order by caf1.cafPostDate desc");
            para.add(caf.getCafSolvedStatus());
        }
        if (page != null) {
            sb.append(" limit " + page.getStart() + "," + page.getSize());
        }
        String sql = sb.toString();
        System.out.println(sql);
        List<CAF> navRes = (ArrayList) conn.queryForArrObject(sql, para, CAF.class);
        return navRes;
    }


    /**
     * 咨询反馈删除（by cafId）
     *
     * @param cafId
     * @return
     * @throws Exception
     */
    public static int cafDelete(String cafId, Connect conn) throws Exception {
        String sql = "delete from egov_consulting_and_feedback where cafId=?";
        List<Object> para = new ArrayList<Object>();
        para.add(cafId);
        return conn.update(sql, para);
    }

    /**
     * 咨询反馈修改
     *
     * @param caf
     * @return
     * @throws Exception
     */
    public static int cafModify(CAF caf, Connect conn) throws Exception {
        StringBuffer sb = new StringBuffer("update egov_consulting_and_feedback set ");
        List<Object> para = new ArrayList<Object>();
        if (StringUtil.isNotEmpty(caf.getCafReplyDate())) {
            sb.append("cafReplyDate=?,");
            para.add(caf.getCafReplyDate());
        }
        if (StringUtil.isNotEmpty(caf.getCafSolvedStatus())) {
            sb.append("cafSolvedStatus=?,");
            para.add(caf.getCafSolvedStatus());
        }
        if (StringUtil.isNotEmpty(caf.getCafReply())) {
            sb.append("cafReply=?");
            para.add(caf.getCafReply());
        }
        if (StringUtil.isNotEmpty(caf.getCafSearchKey())) {
            sb.append("cafSearchKey=?");
            para.add(caf.getCafSearchKey());
        }
        if (StringUtil.isNotEmpty(caf.getCafId())) {
            sb.append(" where cafId=?");
            para.add(caf.getCafId());
        }
        String sql = sb.toString();
        System.out.println(sql);
        return conn.update(sql, para);
    }

    public static int cafCount(CAF caf, Connect conn) throws Exception {
        StringBuffer sb = new StringBuffer("select count(1) from egov_consulting_and_feedback where ");
        List<Object> para = new ArrayList<Object>();
        if (StringUtil.isNotEmpty(caf.getCafId())) {
            sb.append("cafId=?");
            para.add(caf.getCafId());
        }
        if (StringUtil.isNotEmpty(caf.getCafTitle()) && StringUtil.isEmpty(caf.getCafType())) {
            sb.append("cafTitle like ? and ");
            para.add("%" + caf.getCafTitle() + "%");
        }
        if (StringUtil.isNotEmpty(caf.getCafType())) {
            sb.append("cafType=? and ");
            para.add(caf.getCafType());
        }
        if (StringUtil.isNotEmpty(caf.getCafTitle()) && StringUtil.isNotEmpty(caf.getCafType())) {
            sb.append("cafTitle like ? and cafType=? and ");
            para.add("%" + caf.getCafTitle() + "%");
            para.add(caf.getCafType());
        }
        if (StringUtil.isNotEmpty(caf.getCafSearchKey())) {
            sb.append("cafSearchKey=?");
            para.add(caf.getCafSearchKey());
        }
        if (StringUtil.isNotEmpty(caf.getCafPublic())) {
            sb.append("cafPublic=? and date_sub(curdate(), INTERVAL 1 YEAR) <= date(`cafPostDate`) order by cafPostDate desc");
            para.add(caf.getCafPublic());
        }
        if (StringUtil.isNotEmpty(caf.getCafSolvedStatus())) {
            sb.append("cafSolvedStatus=? order by cafPostDate desc");
            para.add(caf.getCafSolvedStatus());
        }
        String sql = sb.toString();
        System.out.println(sql);
        return conn.count(sql, para);
    }

}
